
/*
* Copyright 2017-2018 pinganfang
* All right reserved.
*
*/

package com.pingan.haofang.searchcloud.monitor.dao;

import com.pingan.haofang.searchcloud.common.dto.PageQueryDTO;
import com.pingan.haofang.searchcloud.monitor.convert.ColumnToBean;
import com.pingan.haofang.searchcloud.monitor.domain.DashboardIndexSummaryInfo;
import com.pingan.haofang.searchcloud.monitor.dto.*;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigInteger;
import java.util.List;
import java.util.stream.Collectors;

/**
 * DashboardIndexSummaryInfoDao
 *
 * @author yangbaohu662
 */
@Repository
public interface DashboardIndexSummaryInfoDao
        extends JpaRepository<DashboardIndexSummaryInfo, Long>, JpaSpecificationExecutor<DashboardIndexSummaryInfo> {

    /**
     * 索引视图-获取分页数据
     */
    default Page<DashboardIndexSummaryInfoQueryResult> getConditionPage(DashboardIndexSummaryInfoDTO dto, PageQueryDTO pageQueryDto, EntityManager entityManager) {
        Pageable pageable = new PageRequest(pageQueryDto.getPageNo() - 1, pageQueryDto.getPageSize());

        //分页sql
        StringBuilder sqlBuilder = new StringBuilder("SELECT " +
                " t.project_id AS projectId, " +
                " t.project_name AS projectName, " +
                " COUNT(distinct(t.index_id)) AS indexNum, " +
                " AVG(t.datasource_num) AS datasourceNum, " +
                " AVG(t.avg_cost) AS avgCost, " +
                " AVG(t.record_cost) AS recordCost, " +
                " SUM(t.expect_num) AS expectNum, " +
                " SUM(t.actual_num) AS actualNum, " +
                " SUM(t.actual_success_num) AS actualSuccessNum, " +
                " SUM(t.actual_success_num) / SUM(t.actual_num) AS actualSuccessRate, " +
                " SUM(t.manual_num) AS manualNum, " +
                " SUM(t.manual_success_num) AS manualSuccessNum, " +
                " SUM(t.manual_success_num) / SUM(t.manual_num) AS manualSuccessRate " +
                " FROM t_dashboard_index_summary_info t " +
                " WHERE 1 = 1 ");

        //拼接where条件
        sqlBuilder = appendViewWhereSQL(sqlBuilder, dto);
        //拼接group by
        sqlBuilder.append(" GROUP BY t.project_id,t.project_name");
        //拼接having
        sqlBuilder = appendViewHavingSQL(sqlBuilder, dto);
        //拼接分页条件
        sqlBuilder.append(" limit " + pageable.getOffset() + "," + pageable.getPageSize());

        // 获取分页结果
        Query pageQuery = entityManager.createNativeQuery(sqlBuilder.toString());

        List<DashboardIndexSummaryInfoQueryResult> list = pageQuery.unwrap(SQLQuery.class).
                setResultTransformer(new ColumnToBean(DashboardIndexSummaryInfoQueryResult.class)).list();

        //获取总记录数
        StringBuilder countSqlBuilder = new StringBuilder("SELECT t.project_id AS projectId," +
                "t.project_name AS projectName,AVG(t.avg_cost) AS avgCost, AVG(t.record_cost) AS recordCost " +
                "FROM t_dashboard_index_summary_info t WHERE 1 = 1");

        //拼接where条件
        countSqlBuilder = appendViewWhereSQL(countSqlBuilder, dto);
        //拼接group by
        countSqlBuilder.append(" GROUP BY t.project_id,t.project_name");
        //拼接having
        countSqlBuilder = appendViewHavingSQL(countSqlBuilder, dto);

        //拼接查询记录数sql
        String countSQL = "SELECT COUNT(*) FROM (" + countSqlBuilder.toString() + ") AS tmp";

        //获取记录数
        BigInteger count = (BigInteger) entityManager.createNativeQuery(countSQL).getSingleResult();

        return new PageImpl<DashboardIndexSummaryInfoQueryResult>(list, pageable, count.longValue());
    }

    /**
     * 索引视图-追加where条件
     */
    default StringBuilder appendViewWhereSQL(StringBuilder sqlBuilder, DashboardIndexSummaryInfoDTO dto) {
        if (dto.getProjectId() != null && dto.getProjectId() != -1) {
            sqlBuilder.append(" AND t.project_id =" + dto.getProjectId());
        }

        List<Long> indexId = dto.getIndexId();
        if (CollectionUtils.isNotEmpty(indexId)) {
            String join = StringUtils.join(indexId, ",");
            sqlBuilder.append(" AND t.index_id in (" + join + ")");
        }

        if (dto.getStartDate() != null) {
            sqlBuilder.append(" AND t.data_date >='" + dto.getStartDate() + "'");
        }

        if (dto.getEndDate() != null) {
            sqlBuilder.append(" AND t.data_date <='" + dto.getEndDate() + "'");
        }
        return sqlBuilder;
    }

    /**
     * 索引视图-追加where条件
     */
    default StringBuilder appendViewHavingSQL(StringBuilder sqlBuilder, DashboardIndexSummaryInfoDTO dto) {
        sqlBuilder.append(" HAVING 1=1");

        if (dto.getMinAvgCost() != null) {
            sqlBuilder.append(" AND avgCost >=" + dto.getMinAvgCost());
        }

        if (dto.getMaxAvgCost() != null) {
            sqlBuilder.append(" AND avgCost <=" + dto.getMaxAvgCost());
        }

        if (dto.getMinRecordCost() != null) {
            sqlBuilder.append(" AND recordCost >=" + dto.getMinRecordCost());
        }

        if (dto.getMaxRecordCost() != null) {
            sqlBuilder.append(" AND recordCost <=" + dto.getMaxRecordCost());
        }

        sqlBuilder.append(" ORDER BY t.data_date DESC");

        return sqlBuilder;
    }

    /**
     * 索引视图-获取视图报表
     */
    default List<DashboardIndexSummaryInfoChartResult> getConditionList(DashboardIndexSummaryInfoDTO dto, EntityManager entityManager) {

        StringBuilder sqlBuilder = new StringBuilder("SELECT t.data_date as dataDate, AVG(t.avg_cost) AS avgCost, SUM( IFNULL(t.actual_success_num, 0) + IFNULL(t.manual_success_num, 0)) AS successNum, SUM( IFNULL(t.actual_num, 0) + IFNULL(t.manual_num, 0)) AS totalNum, AVG(t.record_cost) AS recordCost FROM t_dashboard_index_summary_info t WHERE 1 = 1");
        //拼接where条件
        sqlBuilder = appendViewWhereSQL(sqlBuilder, dto);
        //拼接group by
        sqlBuilder.append(" GROUP BY t.data_date");
        //拼接having
        sqlBuilder = appendViewHavingSQL(sqlBuilder, dto);

        // 获取查询结果
        Query pageQuery = entityManager.createNativeQuery(sqlBuilder.toString());

        List<DashboardIndexSummaryInfoChartResult> list = pageQuery.unwrap(SQLQuery.class).
                setResultTransformer(new ColumnToBean(DashboardIndexSummaryInfoChartResult.class)).list();

        return list;
    }


    /**
     * 索引选定索引视图
     */
    default Page<IndexBuildProgressItemDTO> listPageByIndex(DashboardIndexSummaryInfoDTO dto, DashboardIndexRequestDTO indexRequestDTO,
                                                            PageQueryDTO pageQueryDto, EntityManager entityManager) {
        StringBuilder pageBuilder = new StringBuilder("SELECT id, index_name AS indexName, project_code AS projectCode, " +
                "run_type AS runType, STATUS, import_success_count AS recordTotalNum, " +
                "execute_cost_time AS recordTotalCost, IFNULL(execute_cost_time / import_success_count,0) AS avgCost FROM t_index_build_progress" +
                " WHERE status=2 ");

        pageBuilder = appendIndexWhereSQL(pageBuilder, dto, indexRequestDTO);
        //拼接分页条件
        Pageable pageable = new PageRequest(pageQueryDto.getPageNo() - 1, pageQueryDto.getPageSize());
        pageBuilder.append(" limit " + pageable.getOffset() + "," + pageable.getPageSize());
        // 获取分页结果
        Query pageQuery = entityManager.createNativeQuery(pageBuilder.toString());
        List<IndexBuildProgressItemDTO> list = pageQuery.unwrap(SQLQuery.class).
                setResultTransformer(new ColumnToBean(IndexBuildProgressItemDTO.class)).list();

        //获取总记录数
        StringBuilder countSqlBuilder = new StringBuilder("SELECT id," +
                " IFNULL(execute_cost_time / import_success_count,0) AS avgCost FROM t_index_build_progress" +
                " WHERE status=2 ");
        //拼接where条件
        countSqlBuilder = appendIndexWhereSQL(countSqlBuilder, dto, indexRequestDTO);
        //拼接查询记录数sql
        String countSQL = "SELECT COUNT(*) FROM (" + countSqlBuilder.toString() + ") AS tmp";
        //获取记录数
        BigInteger count = (BigInteger) entityManager.createNativeQuery(countSQL).getSingleResult();
        return new PageImpl<IndexBuildProgressItemDTO>(list, pageable, count.longValue());
    }

    default StringBuilder appendIndexWhereSQL(StringBuilder sqlBuilder, DashboardIndexSummaryInfoDTO dto,
                                              DashboardIndexRequestDTO indexRequestDTO) {
        if (dto.getProjectId() != null) {
            sqlBuilder.append(" AND project_code ='" + indexRequestDTO.getProjectCode() + "'");
        }

        List<String> indexNames = indexRequestDTO.getIndexNames();
        if (CollectionUtils.isNotEmpty(indexNames)) {
            indexNames = indexNames.stream().map(x -> "'" + x + "'").collect(Collectors.toList());
            String join = StringUtils.join(indexNames, ",");
            sqlBuilder.append(" AND index_name in (" + join + ")");
        }

        if (dto.getStartDate() != null) {
            sqlBuilder.append(" AND create_time >='" + dto.getStartDate() + "'");
        }

        if (dto.getEndDate() != null) {
            sqlBuilder.append(" AND create_time <='" + dto.getEndDate() + "'");
        }

        if (dto.getMinRecordCost() != null) {
            sqlBuilder.append(" AND  IFNULL( execute_cost_time / import_success_count, 0 ) >=" + dto.getMinRecordCost());
        }

        if (dto.getMaxRecordCost() != null) {
            sqlBuilder.append(" AND  IFNULL( execute_cost_time / import_success_count, 0 )  <=" + dto.getMaxRecordCost());
        }

        sqlBuilder.append(" ORDER BY create_time DESC");
        return sqlBuilder;
    }

    default Page<DashboardIndexSummaryInfoQueryResult> indexViewByProjectOfPage(DashboardIndexSummaryInfoDTO parmDto, PageQueryDTO pageQueryDTO, EntityManager entityManager) {

        //分页sql
        StringBuilder sqlBuilder = new StringBuilder("SELECT t.project_id AS projectId, t.project_name AS projectName, t.index_id AS indexId, t.index_name AS indexName, AVG(t.datasource_num) AS datasourceNum, AVG(t.avg_cost) AS avgCost, AVG(t.record_cost) AS recordCost, SUM(t.expect_num) AS expectNum, SUM(t.actual_num) AS actualNum, SUM(t.actual_success_num) AS actualSuccessNum, SUM(t.actual_success_num) / SUM(t.actual_num) AS actualSuccessRate, SUM(t.manual_num) AS manualNum, SUM(t.manual_success_num) AS manualSuccessNum, SUM(t.manual_success_num) / SUM(t.manual_num) AS manualSuccessRate FROM t_dashboard_index_summary_info t WHERE 1 = 1");

        //拼接where条件
        sqlBuilder = appendViewWhereSQL(sqlBuilder, parmDto);
        //拼接group by
        sqlBuilder.append(" GROUP BY t.project_id,t.project_name,t.index_id,t.index_name");
        //拼接having
        sqlBuilder = appendViewHavingSQL(sqlBuilder, parmDto);

        Pageable pageable = new PageRequest(pageQueryDTO.getPageNo() - 1, pageQueryDTO.getPageSize());
        //拼接分页条件
        sqlBuilder.append(" limit " + pageable.getOffset() + "," + pageable.getPageSize());

        // 获取分页结果
        Query pageQuery = entityManager.createNativeQuery(sqlBuilder.toString());

        List<DashboardIndexSummaryInfoQueryResult> list = pageQuery.unwrap(SQLQuery.class).
                setResultTransformer(new ColumnToBean(DashboardIndexSummaryInfoQueryResult.class)).list();

        //获取总记录数
        StringBuilder countSqlBuilder = new StringBuilder("SELECT t.project_id AS projectId," +
                "t.project_name AS projectName,AVG(t.avg_cost) AS avgCost, AVG(t.record_cost) AS recordCost " +
                "FROM t_dashboard_index_summary_info t WHERE 1 = 1");

        //拼接where条件
        countSqlBuilder = appendViewWhereSQL(countSqlBuilder, parmDto);
        //拼接group by
        countSqlBuilder.append(" GROUP BY t.project_id,t.project_name,t.index_id,t.index_name");
        //拼接having
        countSqlBuilder = appendViewHavingSQL(countSqlBuilder, parmDto);

        //拼接查询记录数sql
        String countSQL = "SELECT COUNT(*) FROM (" + countSqlBuilder.toString() + ") AS tmp";

        //获取记录数
        BigInteger count = (BigInteger) entityManager.createNativeQuery(countSQL).getSingleResult();

        return new PageImpl<>(list, pageable, count.longValue());
    }

    default List<IndexChartItemDTO> avgCostByIndex(DashboardIndexSummaryInfoDTO dto, PageQueryDTO pageQueryDto, EntityManager entityManager) {
        //分页sql
        StringBuilder sqlBuilder = new StringBuilder(
                "SELECT project_id AS projectId,project_name AS projectName, " +
                        "index_id AS indexId,index_name AS indexName, data_date AS dataDate, avg_cost AS avgCost FROM " +
                        "t_dashboard_index_summary_info WHERE 1=1 ");

        if (dto.getProjectId() != null) {
            sqlBuilder.append(" AND project_id =" + dto.getProjectId());
        }

        if (CollectionUtils.isNotEmpty(dto.getIndexId())) {
            String join = StringUtils.join(dto.getIndexId(), ",");
            sqlBuilder.append(" AND index_id in (" + join + ")");
        }

        if (dto.getStartDate() != null) {
            sqlBuilder.append(" AND data_date >='" + dto.getStartDate() + "'");
        }

        if (dto.getEndDate() != null) {
            sqlBuilder.append(" AND data_date <='" + dto.getEndDate() + "'");
        }

        if (dto.getMinAvgCost() != null) {
            sqlBuilder.append(" AND avg_cost >=" + dto.getMinAvgCost());
        }

        if (dto.getMaxAvgCost() != null) {
            sqlBuilder.append(" AND avg_cost <=" + dto.getMaxAvgCost());
        }

        //拼接group by
        sqlBuilder.append(" GROUP BY project_id, index_id, data_date");

        // 获取分页结果
        Query pageQuery = entityManager.createNativeQuery(sqlBuilder.toString());

        List<IndexChartItemDTO> list = pageQuery.unwrap(SQLQuery.class).
                setResultTransformer(new ColumnToBean(IndexChartItemDTO.class)).list();

        return list;
    }
}
